package com.tmpt.utils.report;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.pdf.*;
import com.tmpt.dto.A1TemplateColumnDto;
import com.tmpt.dto.A1TemplateTableDto;
import com.tmpt.dto.Result;
import com.tmpt.utils.DateTool;
import com.tmpt.utils.StringUtils;
import com.tmptSource.dto.OwrDoubleTaskPairDto;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;


public class ExcelUtils {

	/**
	 * 报表下载 （response返回模式）,显示查询条件
	 * @param title
	 * @param reportList
	 * @param response
	 * @throws IOException
	 */
	public static void downloadReport(String title , String condition, List<List<String>> reportList, HttpServletResponse response) throws IOException{
		HSSFWorkbook wb = downloadReport(title,condition,reportList);
		String fileName = title+".xls";
		java.io.BufferedOutputStream out = new java.io.BufferedOutputStream(response.getOutputStream());
		response.setContentType("application/octet-steam");
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename="+fileName);
		wb.write(out);
		out.flush();
		out.close();
	}
	/**
	 * 报表下载 （response返回模式）
	 * @param title
	 * @param reportList
	 * @param response
	 * @throws IOException
	 */
	public static void downloadReport(String title ,List<List<String>> reportList, HttpServletResponse response) throws IOException{
		HSSFWorkbook wb = downloadReport(title,"",reportList);
		String fileName = title+".xls";
		java.io.BufferedOutputStream out = new java.io.BufferedOutputStream(response.getOutputStream());
		response.setContentType("application/octet-steam");
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename="+fileName);
		wb.write(out);
		out.flush();
		out.close();
	}

	/**
	 * 报表下载（生成本地文件模式）
	 * @param title
	 * @param reportList
	 * @param targetPath
	 * @throws IOException
	 */
	public static void downloadReport(String title ,List<List<String>> reportList, String targetPath) throws IOException{
		HSSFWorkbook wb = downloadReport(title,"",reportList);
		String fileName = targetPath+"/"+title+".xls";
		FileOutputStream fos = new FileOutputStream(fileName);
		wb.write(fos);
		fos.close();
	}

	private static HSSFWorkbook downloadReport(String title , String condition,List<List<String>> reportList) throws IOException{
		if(reportList.size()==0){
			return null;
		}

		List<String> firstRow = reportList.get(0);
		int columnSize =firstRow.size();

		//Excel
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		int nRow=0;
		int nCol=0;
		//style
		HSSFCellStyle style1 = wb.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment((short) 1);
		style1.setBorderBottom((short) 1);
		style1.setBorderLeft((short) 1);
		style1.setBorderRight((short) 1);
		style1.setBorderTop((short) 1);
		style1.setWrapText(true);

	    HSSFCellStyle style2 = wb.createCellStyle();
	    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment((short)1);
		style2.setBorderBottom((short)1);
		style2.setBorderLeft((short)1);
		style2.setBorderRight((short)1);
		style2.setBorderTop((short)1);
	    //style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    //style2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

	    HSSFCellStyle style3 = wb.createCellStyle();
	    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    style3.setVerticalAlignment((short)1);
	    style3.setBorderBottom((short)1);
	    style3.setBorderLeft((short)1);
	    style3.setBorderRight((short)1);
	    style3.setBorderTop((short)1);
		//HSSFDataFormat format = wb.createDataFormat();
		//style3.setDataFormat(format.getFormat("##0.00")); //  6位小数

		short lineHeight = (short)350;
		//Title
		HSSFRow row = sheet.createRow(nRow);
		HSSFCell cell = row.createCell((short)nCol++);//新列
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style1);
		String downTime = "下载日期:"+DateTool.getCurrentDateStr();
		if(StringUtils.isNull(condition)){
			cell.setCellValue(title+" \r\n ("+downTime+")");
			row.setHeight((short) (2 * lineHeight));
		}else {
			cell.setCellValue(title + " \r\n " + condition  + " \r\n (" + downTime + ")");
			row.setHeight((short)(3*lineHeight));
		}
		sheet.addMergedRegion(new CellRangeAddress(nRow, (short) 0, nRow, (short) (columnSize - 1)));
		for(int i=0;i<columnSize-1;i++){
			cell = row.createCell((short)nCol++);//新列
			cell.setCellStyle(style1);
		}
		nRow++;

		//列宽
		Map<Integer,Short> columnWidthMap = new HashMap<Integer,Short>();

		//2 Row
		nCol=0;
		row = sheet.createRow(nRow);//新建行
		row.setHeight(lineHeight);
		for(String firstRowColumnValue:firstRow){
			cell = row.createCell((short)nCol);//新建列
			//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellStyle(style2);
			cell.setCellValue(firstRowColumnValue);
			//列宽
			short width = (short)firstRowColumnValue.getBytes().length;
			columnWidthMap.put(nCol, width);
			nCol++;
		}
		nRow++;

		int currentRowIndex = 0;
		for(List<String> row1 : reportList){
			if(currentRowIndex++ == 0){continue;}
			nCol=0;
			row = sheet.createRow(nRow);//新建行
			row.setHeight(lineHeight);

			for(String firstRowColumnValue:row1){
				cell = row.createCell((short)nCol);//新建列
				cell.setCellStyle(style3);
				try {
					if(firstRowColumnValue.length()>10){
						throw new Exception("");
					}
					Double.parseDouble(firstRowColumnValue);
					cell.setCellValue(new BigDecimal(firstRowColumnValue).doubleValue());
				} catch (Exception e) {
					//String 类型
					if(firstRowColumnValue==null)firstRowColumnValue="";
					cell.setCellValue(firstRowColumnValue);
				}

				//列宽
				short widthCur = columnWidthMap.get(nCol);
				short widthNew = (short)firstRowColumnValue.toString().getBytes().length;
				if(widthNew>widthCur){
					columnWidthMap.put(nCol,widthNew);
				}

				nCol++;
			}
			nRow++;

		}

		//设置列宽
		for(int c=0;c<columnSize;c++){
			short w = (short)(columnWidthMap.get(c) * 350);
			sheet.setColumnWidth((short)c,w);
		}

		return wb;
	}

	/**
	 * 模板报表下载
	 * @param reportList
	 * @param response
	 * @return
	 * @throws IOException
	 */
	public static Result downloadReport4Template(List<Map<String,Object>> reportList,A1TemplateTableDto templateTable, HttpServletResponse response) throws IOException{
		List<A1TemplateColumnDto> columnList = templateTable.getA1TemplateColumnDtoList();
		int columnSize =columnList.size();

		//Excel
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		int nRow=0;
		int nCol=0;
		//style
		HSSFCellStyle style1 = wb.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment((short)1);
		style1.setBorderBottom((short)1);
		style1.setBorderLeft((short)1);
		style1.setBorderRight((short)1);
		style1.setBorderTop((short)1);

	    HSSFCellStyle style2 = wb.createCellStyle();
	    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment((short)1);
		style2.setBorderBottom((short)1);
		style2.setBorderLeft((short)1);
		style2.setBorderRight((short)1);
		style2.setBorderTop((short)1);
	    //style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    //style2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

	    HSSFCellStyle style3 = wb.createCellStyle();
	    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    style3.setVerticalAlignment((short)1);
	    style3.setBorderBottom((short)1);
	    style3.setBorderLeft((short)1);
	    style3.setBorderRight((short)1);
	    style3.setBorderTop((short)1);
		//HSSFDataFormat format = wb.createDataFormat();
		//style3.setDataFormat(format.getFormat("##0.00")); //  6位小数

		short lineHeight = (short)350;
		//Title
		HSSFRow row = sheet.createRow(nRow);
		row.setHeight(lineHeight);
		HSSFCell cell = row.createCell((short)nCol++);//新列
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style1);
		String downTime = "下载日期:"+DateTool.getCurrentDateStr();
		cell.setCellValue(templateTable.getTableNameShow()+"  ("+downTime+")");
		sheet.addMergedRegion(new CellRangeAddress(nRow, (short) 0, nRow, (short) (columnSize - 1)));
		for(int i=0;i<columnSize-1;i++){
			cell = row.createCell((short)nCol++);//新列
			cell.setCellStyle(style1);
		}
		nRow++;

		/*
		//Time
		nCol=0;
		row = sheet.createRow(nRow);
		row.setHeight(lineHeight);
		cell = row.createCell((short)nCol++);//新列
		//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellStyle(style1);
		cell.setCellValue("下载日期:"+DateTool.getCurrentDateStr());
///		sheet.addMergedRegion(new CellRangeAddress(nRow,0,nRow,(columnSize-1)));
		for(int i=0;i<columnSize-1;i++){
			cell = row.createCell((short)nCol++);//新列
			cell.setCellStyle(style1);
		}
		nRow++;
		*/

		//列宽
		Map<Integer,Short> columnWidthMap = new HashMap<Integer,Short>();

		//2 Row
		nCol=0;
		row = sheet.createRow(nRow);//新建行
		row.setHeight(lineHeight);
		for(A1TemplateColumnDto a1TemplateColumnDto:columnList){
			String columnShowName = a1TemplateColumnDto.getColumnShowName();
			String columnName = a1TemplateColumnDto.getColumnName();
			cell = row.createCell((short)nCol);//新建列
			//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellStyle(style2);
			cell.setCellValue(columnShowName);
			//列宽
			short width = (short)columnShowName.getBytes().length;
			columnWidthMap.put(nCol, width);

			nCol++;
		}
		System.out.println(columnWidthMap);
		nRow++;

		for(Map<String,Object> map : reportList){
			nCol=0;
			row = sheet.createRow(nRow);//新建行
			row.setHeight(lineHeight);

			for(A1TemplateColumnDto a1TemplateColumnDto:columnList){
				String columnName = a1TemplateColumnDto.getColumnName();
				Iterator<String> it = map.keySet().iterator();
				while(it.hasNext()){
					String key = it.next();
					if(key.equals(columnName)){

						Object value = map.get(key);
						cell = row.createCell((short)nCol);//新建列
						//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
						cell.setCellStyle(style3);

						try {
							Double.parseDouble(value.toString());
							cell.setCellValue(new BigDecimal(value.toString()).doubleValue());
						} catch (Exception e) {
							//String 类型
							if(value==null)value="";
							cell.setCellValue(value.toString());
						}

						//列宽
						short widthCur = columnWidthMap.get(nCol);
						short widthNew = (short)value.toString().getBytes().length;
						if(widthNew>widthCur){
							columnWidthMap.put(nCol,widthNew);
						}
						//
						nCol++;
						break;
					}
				}
			}

			nRow++;
		}

		//设置列宽
		for(int c=0;c<columnSize;c++){
			//sheet.autoSizeColumn(c);
			short w = (short)(columnWidthMap.get(c) * 350);
			sheet.setColumnWidth((short)c,w);
		}

		String fileName = templateTable.getTableNameShow()+"报表.xls";
		java.io.BufferedOutputStream out = new java.io.BufferedOutputStream(response.getOutputStream());
		response.setContentType("application/octet-steam");
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename="+fileName);
		wb.write(out);
		out.flush();
		out.close();

		return null;
	}


	public static void main(String[] args) {

		String dealTimeStr = "";
		try{
			dealTimeStr = DateTool.date2String(DateTool.string2DateForAnyFormat("2011-11-23 11:01:33","yyyy-MM-dd"),"yyyy年MM月dd日");
		}catch (Exception e){}
		System.out.println(dealTimeStr);
	}

	/**
	 * 双随机报表
	 * @param title
	 * @param owrDoubleTaskPairDto
	 * @param response
	 * @throws IOException
	 */
	public static void downloadReport4DoubleRandom(String title , OwrDoubleTaskPairDto owrDoubleTaskPairDto, HttpServletResponse response) throws IOException{
		//Excel
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");

		// 生成一个字体
		HSSFFont fontBold18 = wb.createFont();
		fontBold18.setFontHeightInPoints((short) 18);
		//font.setColor(HSSFColor.RED.index);
		fontBold18.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontBold18.setFontName("宋体");

		HSSFFont fontBold16 = wb.createFont();
		fontBold16.setFontHeightInPoints((short) 16);
		//font.setColor(HSSFColor.RED.index);
		fontBold16.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		fontBold16.setFontName("宋体");

		HSSFFont font14 = wb.createFont();
		font14.setFontHeightInPoints((short) 14);
		//font.setColor(HSSFColor.RED.index);
		font14.setFontName("宋体");

		HSSFFont font12 = wb.createFont();
		font12.setFontHeightInPoints((short) 12);
		font12.setFontName("宋体");

		//style
		HSSFCellStyle styleBig = wb.createCellStyle();
		styleBig.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleBig.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		styleBig.setWrapText(true);
		styleBig.setFont(fontBold18);

		HSSFCellStyle styleMid = wb.createCellStyle();
		styleMid.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleMid.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		styleMid.setWrapText(true);
		styleMid.setFont(fontBold16);



		HSSFCellStyle style1 = wb.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		style1.setBorderBottom((short) 1);
		style1.setBorderLeft((short) 1);
		style1.setBorderRight((short) 1);
		style1.setBorderTop((short) 1);
		style1.setWrapText(true);
		style1.setFont(font14);

		HSSFCellStyle style_left = wb.createCellStyle();
		style_left.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平居中
		style_left.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		style_left.setBorderBottom((short)1);
		style_left.setBorderLeft((short)1);
		style_left.setBorderRight((short)1);
		style_left.setBorderTop((short)1);
		style_left.setWrapText(true);
		style_left.setFont(font12);

		HSSFCellStyle style_left2 = wb.createCellStyle();
		style_left2.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平居中
		style_left2.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);//垂直居中
		style_left2.setWrapText(true);
		style_left2.setFont(font12);

		HSSFCellStyle style_right = wb.createCellStyle();
		style_right.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//水平居中
		style_right.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
		style_right.setWrapText(true);
		style_right.setFont(font12);
		//style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		//style2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

		//
		int lineHeight = 350;
		HSSFRow row;
		HSSFCell cell;
		sheet.setColumnWidth(0,20*lineHeight);
		sheet.setColumnWidth(1,45*lineHeight);

		//0 row
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
		row = sheet.createRow(0);
		row.setHeight((short) (2 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(styleBig);
		//cell.setCellValue("_______区__________（街镇）城管执法中队");
		cell.setCellValue("浦东新 区 陆家嘴 （街镇）城管执法中队");
		//1 row
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
		row = sheet.createRow(1);
		row.setHeight((short) (2 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(styleMid);
		cell.setCellValue("现场检查记录单（各中队留存备查）");

		//2 row
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
		row = sheet.createRow(2);
		row.setHeight((short) (2 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style_right);
		String dealTimeStr = "";
		try{
			dealTimeStr = DateTool.date2String(DateTool.string2DateForAnyFormat(owrDoubleTaskPairDto.getDealTime(),"yyyy-MM-dd"),"yyyy年MM月dd日");
		}catch (Exception e){}
		cell.setCellValue("检查日期："+ dealTimeStr );

		//3 -6 row
		int owrType = Integer.parseInt(owrDoubleTaskPairDto.getOwrType());
		sheet.addMergedRegion(new CellRangeAddress(3, 6, 0, 0));
		row = sheet.createRow(3);
		cell = row.createCell((short)0);
		cell.setCellStyle(style1);
		cell.setCellValue("检查类别");

		cell = row.createCell((short)1);
		row.setHeight((short) (1 * lineHeight));
		cell.setCellStyle(style_left);
		cell.setCellValue("跨门经营（    ）");

		row = sheet.createRow(4);
		row.setHeight((short) (1 * lineHeight));
		cell = row.createCell((short)1);
		cell.setCellStyle(style_left);
		if(owrType==2) {
			cell.setCellValue("餐厨垃圾、废弃油脂产生单位（  Y ）");
		}else {
			cell.setCellValue("餐厨垃圾、废弃油脂产生单位（    ）");
		}

		row = sheet.createRow(5);
		row.setHeight((short) (1 * lineHeight));
		cell = row.createCell((short)1);
		cell.setCellStyle(style_left);
		cell.setCellValue("生活垃圾（   ）");

		row = sheet.createRow(6);
		row.setHeight((short) (1 * lineHeight));
		cell = row.createCell((short)1);
		cell.setCellStyle(style_left);
		if(owrType==1) {
			cell.setCellValue("建筑工地、中转码头（  Y  )");
		}else{
			cell.setCellValue("建筑工地、中转码头（     )");
		}

		//7 row
		row = sheet.createRow(7);
		row.setHeight((short) (2 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style1);
		cell.setCellValue("检查对象");

		cell = row.createCell(1);
		cell.setCellStyle(style_left);
		cell.setCellValue(owrDoubleTaskPairDto.getEntityName());

		//8 row
		row = sheet.createRow(8);
		row.setHeight((short) (10 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style1);
		cell.setCellValue("检查内容");

		cell = row.createCell(1);
		cell.setCellStyle(style_left);
		cell.setCellValue(owrDoubleTaskPairDto.getImproveRequied());


		//9 row
		row = sheet.createRow(9);
		row.setHeight((short) (6 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style1);
		cell.setCellValue("存在问题");

		cell = row.createCell(1);
		cell.setCellStyle(style_left);
		cell.setCellValue(owrDoubleTaskPairDto.getDealIssueContent());

		//10 row
		row = sheet.createRow(10);
		row.setHeight((short) (6 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style1);
		cell.setCellValue("处理意见");

		cell = row.createCell(1);
		cell.setCellStyle(style_left);
		cell.setCellValue(owrDoubleTaskPairDto.getDealResult());


		//11 row
		row = sheet.createRow(11);
		row.setHeight((short) (2 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style1);
		cell.setCellValue("检查人员（签名）");

		cell = row.createCell(1);
		cell.setCellStyle(style_left);
		cell.setCellValue(owrDoubleTaskPairDto.getUserName());

		//12 row
		sheet.addMergedRegion(new CellRangeAddress(12, 12, 0, 1));
		row = sheet.createRow(12);
		row.setHeight((short) (6 * lineHeight));
		cell = row.createCell((short)0);
		cell.setCellStyle(style_left2);
		cell.setCellValue("\n填写说明：\n" +
				"     1.检查类别在（  ）内打勾选择；\n" +
				"     2.跨门经营的检查对象填写路段名称，如“丽园路（徐家汇路~鲁班路）”；检查对象是单位的，名称填写营业执照名称，无证单位填写“店招店牌名（经营地址）”；建设工地填写建设项目名称。\n");



		String fileName = title+".xls";
		java.io.BufferedOutputStream out = new java.io.BufferedOutputStream(response.getOutputStream());
		response.setContentType("application/octet-steam");
		fileName = URLEncoder.encode(fileName, "UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename="+fileName);
		wb.write(out);
		out.flush();
		out.close();

	}



}
